BigQueryとWorkflowsで特定の条件に該当するユーザーを抽出してLINE公式アカウントのオーディエンスを一括作成してみた

export type Config = {
    projectId: string
    location: string
    lineClientId: string
}

export const getConfig = (): Config => {
    return {
        projectId: '<Google CloudプロジェクトのID>',
        location: 'asia-northeast1',
        lineClientId: '<LINE公式アカウントのChannel ID>'    
    }
}

import { App } from "cdktf";
import { LineAudienceStack } from "./stacks/line-audience-stack";
import { getConfig } from "./config";

const app = new App();
const config = getConfig();;
new LineAudienceStack(app, "line_audience_stack", {
  projectId: config.projectId,
  location: config.location,
  lineClientId: config.lineClientId
});
app.synth();

import { Construct } from "constructs";
import { TerraformStack } from "cdktf";
import * as google from "@cdktf/provider-google";
import { DwhConstruct } from "../constructs/dwh";
import { RandomProvider } from "@cdktf/provider-random/lib/provider";
import { WorkflowConstruct } from "../constructs/workflow";
import { Config } from "../config";

type LineAudienceStackProps = Config;

export class LineAudienceStack extends TerraformStack {
  constructor(scope: Construct, id: string, props: LineAudienceStackProps) {
    super(scope, id);

    const {location, projectId} = props;
    new google.provider.GoogleProvider(this, 'GoogleProvider', {
        project: projectId,
      });

    new RandomProvider(this, 'random-provider');

    const dwh = new DwhConstruct(this, 'dwh', {
      location,
    });

    new WorkflowConstruct(this, "workflow", {
      location,
      datasetId: dwh.datasetId,
      lineClientId: props.lineClientId,
      tableName: dwh.tableName,
    });
  }
}

import { Construct } from "constructs";
import * as google from "@cdktf/provider-google";
import { StringResource } from '@cdktf/provider-random/lib/string-resource';
import { resolve }  from 'path';
import { readFileSync, readdirSync }  from 'fs';

type DwhConstructProps = {
    location: string;
}

export class DwhConstruct extends Construct {

  readonly datasetId: string
  readonly tableName: string

  constructor(scope: Construct, id: string, props: DwhConstructProps) {
    super(scope, id);

    const { location } = props;
    this.datasetId = 'line_dataset';
    const dataset = new google.bigqueryDataset.BigqueryDataset(this, 'dataset', {
        datasetId: this.datasetId,
        friendlyName: 'Line Dataset',
        description: 'Line Dataset',
        location,
    })

    const bucketSuffix = new StringResource(this, 'randomString', {
        length: 8,
        special: false,
        upper: false,
      }).result;

    const bucket = new google.storageBucket.StorageBucket(this, 'bucket', {
        name: `line-audience-bucket-${bucketSuffix}`,
        location,
        forceDestroy: true,
    })

    const assetsDir = resolve(__dirname, '..', 'assets', 'line-users');    
    readdirSync(assetsDir).map((file, idx) => {
        new google.storageBucketObject.StorageBucketObject(this, `object_${idx}`, {
            bucket: bucket.id,
            name: file,
            content: readFileSync(`${assetsDir}/${file}`).toString(),
        })
    })    

    this.tableName = 'line_users';
    new google.bigqueryTable.BigqueryTable(this, 'line_users_table', {
        datasetId: dataset.datasetId,
        tableId: this.tableName,
        externalDataConfiguration: {
            sourceFormat: 'NEWLINE_DELIMITED_JSON',
            sourceUris: [
                `gs://${bucket.name}/*`
            ],
            autodetect: false,
            schema: JSON.stringify([
                {
                    "name": "id",
                    "type": "STRING",
                    "mode": "REQUIRED",
                    "description": "LINEのユーザーID"
                },
                {
                    "name": "pref",
                    "type": "STRING",
                    "mode": "REQUIRED",
                    "description": "都道府県"
                },
            ])
        },
        deletionProtection: false,
    })
  }
}

import { Construct } from "constructs";
import { Fn } from 'cdktf';
import { resolve } from 'path';
import * as google from "@cdktf/provider-google";

type WorkflowConstructProps = {
    location: string;
    datasetId: string;
    lineClientId: string;
    tableName: string;
}

export class WorkflowConstruct extends Construct {
  constructor(scope: Construct, id: string, props: WorkflowConstructProps) {
    super(scope, id);

    const workflowsSa = new google.serviceAccount.ServiceAccount(this, 'workflow_sa', {
        accountId: 'create-line-audience-sa',
        displayName: 'Line Workflow Service Account',
      });

    new google.projectIamMember.ProjectIamMember(this, 'bq_data_viewer', {
        role: 'roles/bigquery.dataViewer',
        project: workflowsSa.project,
        member: workflowsSa.member,
    })

    new google.projectIamMember.ProjectIamMember(this, 'bq_job_user', {
      role: 'roles/bigquery.jobUser',
      project: workflowsSa.project,
      member: workflowsSa.member,
    })

    new google.projectIamMember.ProjectIamMember(this, 'gcs_obj_viewer', {
      role: 'roles/storage.objectViewer',
      project: workflowsSa.project,
      member: workflowsSa.member,
    })

    new google.projectIamMember.ProjectIamMember(this, 'log_writer', {
      role: 'roles/logging.logWriter',
      project: workflowsSa.project,
      member: workflowsSa.member,
    })

    const secretId = 'line_secret'
    const secret = new google.secretManagerSecret.SecretManagerSecret(this, 'line_secret', {
      secretId,
      replication: {
        auto: {}
      }
    })

    new google.secretManagerSecretVersion.SecretManagerSecretVersion(this, 'line_secret_version', {
      secret: secret.id,
      secretData: 'デプロイ後に手動で書き換える',
      lifecycle: {
        ignoreChanges: 'all'
      }
    })

    new google.secretManagerSecretIamMember.SecretManagerSecretIamMember(this, 'secret_accessor', {
      secretId: secret.secretId,
      role: 'roles/secretmanager.secretAccessor',
      member: workflowsSa.member,
    });

    const templatePath = resolve(__dirname, '..', 'workflow.yaml');
    const templateFile = Fn.templatefile(templatePath, {
      lineClientId: props.lineClientId,
      secretId,
      dataset: props.datasetId,
      table: props.tableName,
    });
    new google.workflowsWorkflow.WorkflowsWorkflow(this, 'Default', {
        description: 'BigQueryのクエリ結果からLineのAudienceを作成するワークフロー',
        serviceAccount: workflowsSa.email,
        name: 'create-line-audience-workflow',
        region: props.location,
        sourceContents: templateFile,
    });
  }
}

main:
  params: [ args ]
  steps:
    - init:
        assign:
          - project_id: $${sys.get_env("GOOGLE_CLOUD_PROJECT_ID")}
          - secret_id: ${secretId}
    - exec_query:
        call: googleapis.bigquery.v2.jobs.query
        args:
          projectId: $${project_id}
          body:
            query: |
                SELECT
                  TO_JSON_STRING(
                    ARRAY_AGG(
                      JSON_OBJECT('id', id)
                    )
                  ) AS audiences
                FROM
                  ${dataset}.${table}
                WHERE
                  pref = '兵庫県'
            useLegacySql: false
        result: query_result
    - fetch_client_secret:
        call: googleapis.secretmanager.v1.projects.secrets.versions.access
        args:
            name: $${"projects/" + project_id + "/secrets/" + secret_id + "/versions/latest"}
        result: fetch_secret_result
    - fetch_token:
        call: http.post
        args:
            url: https://api.line.me/v2/oauth/accessToken
            headers:
              Content-Type: application/x-www-form-urlencoded
            body:
              grant_type: client_credentials
              client_id: ${lineClientId}
              client_secret: $${ text.decode(base64.decode(fetch_secret_result.payload.data)) }
        result: fetch_token_result
    - create_audience:
        call: http.post
        args:
            url: https://api.line.me/v2/bot/audienceGroup/upload
            headers:
              Authorization: $${"Bearer " + fetch_token_result.body.access_token}
            body:
              description: Test From Google Cloud
              uploadDescription: Google CloudのWorkflowから作成
              audiences: $${json.decode(query_result.rows[0].f[0].v)}

SELECT
  TO_JSON_STRING(
    ARRAY_AGG(
      JSON_OBJECT('id', id)
    )
  ) AS audiences
FROM
  line_dataset.line_users
WHERE
  pref = '兵庫県'

{"f":[{"v":[{"v":"{\"id\":456}"}]}]}

client_secret: $${ text.decode(base64.decode(fetch_secret_result.payload.data)) }

{"f":[{"v":"[{\"id\":\"456\"}]"}]}